set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
set spark.sql.crossJoin.enabled=true;

-- 日汇总
with lmdm_sys_staff as (
    select
       a.staff_no as staff_code,  --编码
       a.name     as staff_name,  --名字
       a.post_name,  --岗位名字
       a.post_code,  -- 岗位code
       a.network_id,  --网点id
       b.code as network_code,  --网点code
       a.network_name,  --网点名字
       a.create_time as hire_time, -- 创建时间
       case
           when date_format(add_months('{{ execution_date | cst_ds }}',-2),'yyyy-MM-01') <= date_format(a.create_time, 'yyyy-MM-01')  then 1
           when date_format(add_months('{{ execution_date | cst_ds }}',-2),'yyyy-MM-01') >  date_format(a.create_time, 'yyyy-MM-01')  then 0
       end as is_new, -- 是否新员工
       1 as  type1
    from jms_dim.dim_lmdm_sys_user a
        left join jms_dim.dim_network_whole_massage b
            on a.network_id = b.id
    where  a.incumbency_status=1
      and  a.is_delete=1
      and  a.is_enable=1
      and  a.post_code='0184'
      and  upper(a.staff_no) not like 'LS%'
      and  a.network_name  not like '%BEST%'
   ),
     staff_rank_detail as (
         select
             delivery_code
              , NULL AS delivery_name
              , create_day
              , sum(if(first_type_name = '投诉' , 1, 0))   as complaint_num -- 投诉数量
              , sum(if(second_type_name = '服务态度',1,0))  as fwtd_num      -- 服务态度客诉
         from jms_dm.dm_waybill_customer_complaint_detail_dt
         where dt = '{{ execution_date | cst_ds }}'
         and delivery_code is not null
           and is_delete = 2
           -- and work_type in ('普通工单', '平台工单')
         group by delivery_code, create_day
     ),
-- 末端时效  OK
terminal_sign_summary_plan_sign as (
    select
        dt,                --业务日期
        final_sign_user_code,                                    --理货员code
        max(final_sign_user_name)    as final_sign_user_name,    --理货员名字
        sum(need_sign_count)         as need_sign_count,         -- 应签收
        sum(all_sign_22)     as all_sign_22,         --22点签收量
        coalesce(round((sum(all_sign_22) / sum(need_sign_count)) * 100, 2), 0.00) as isSignRate               -- 22点签收率
    from jms_dm.dm_terminal_sign_punctuality_rate_dt a
             left semi join
                lmdm_sys_staff b
                    on a.final_sign_user_code = b.staff_code
                   and a.final_sign_network_code = b.network_code
    where dt >= trunc('{{ execution_date | cst_ds }}','MM')
      and dt<='{{ execution_date | cst_ds }}'
    group by dt,final_sign_user_code
     ),
-- 全程时效分析-汇总表（时效签收维度
--      duration_of_whole_process as (
--            select
--                 dt as  sign_date,
--                  deliver_user_code as deliver_later_user_code,
--                  max(deliver_user_name) as deliver_later_user,
--                  sum(deliver_aging_sign_time) as deliver_aging_sign_time,     -- 时效签收和末端网点派件时间差
--                  sum(deliver_aging_sign_cnt) as deliver_aging_sign_cnt,       --时效签收和末端网点派件票数
--           sum(deliver_aging_sign_time)/sum(deliver_aging_sign_cnt) as delver_sign_avg_hour
--     from jms_dm.dm_whole_effect_deliver_summary_dt
--         where dt>=trunc('{{ execution_date | cst_ds }}','MM')
--           and dt<='{{ execution_date | cst_ds }}'
--    group by dt,deliver_user_code
--      ),
 -- 这个需要换表
 outfield_person_order as (  -- 数据驾舱散单逻辑引用至大数据
     SELECT  
            date_time         --日期 
            ,pick_staff_code  -- 员工code 
            ,MAX(pick_staff_name)                                                                  AS pick_staff_name        -- 员工名字 
            ,SUM(taking_success_sum)                                                               AS taking_success_sum     --揽收成功量
            ,SUM(order_taking_fail_sum)                                                            AS order_taking_fail_sum  --揽收失败量
            ,round(SUM(taking_success_sum)/(SUM(taking_success_sum)+SUM(order_taking_fail_sum)),4) AS taking_success_rate
        FROM
        (
            SELECT  
                dt                                    AS date_time --业务日期 
                ,pick_staff_code --取件业务员编码 
                ,MAX(pick_staff_name)                 as pick_staff_name --取件业务员名字 
                ,SUM(if(order_status_code = 103,1,0)) AS taking_success_sum --揽收成功总量 
                ,SUM(if(order_status_code = 104,1,0)) AS order_taking_fail_sum --揽收失败总量 
                ,dt --分区 
            FROM jms_dwd.dwd_yl_oms_oms_order_incre_dt t1
            JOIN jms_dim.dim_tab_channel_mapping_online_base b on t1.order_source_code = b.channel_code
            WHERE t1.dt >= trunc('{{ execution_date | cst_ds }}', 'MM')
                  AND t1.dt <= '{{ execution_date | cst_ds }}'
                  AND t1.is_delete = 1
            GROUP BY  dt
                     ,pick_staff_code
        ) t1
        GROUP BY  date_time
                ,pick_staff_code
     ),
-- 派送途中遗失
dispatch_loss as (
    select
        dt as date_time
        ,dispatch_staff_code
        ,dispatch_staff_name
        ,count(*)     as dispatch_loss_cnt -- 派送途中遗失量
    from
        jms_dm.dm_sqs_loss_detail_dt
    where
        dt >= trunc('{{ execution_date | cst_ds }}', 'MM')
        AND dt <= '{{ execution_date | cst_ds }}'
        and second_type_code = '0316'
    group by
        dt,dispatch_staff_code,dispatch_staff_name
)
-- 工服抽查
,   uniform_check as (
    select
        substr(nvl(audit_time,complete_time),1,10) as date_time
        ,user_code
        ,user_name
        ,count(*)   as uniform_num  -- 工服抽查
        ,sum(if(review_status = 'SUCCESS',1,0))                         as uniform_success_num
        ,sum(if(review_status = 'FAIL' or review_status is null,1,0))   as uniform_fail_num
    from
         jms_dwd.dwd_spot_check_task_detail_dt
    where
        dt >= date_add(trunc('{{ execution_date | cst_ds }}', 'MM'),-10)
        AND dt <= '{{ execution_date | cst_ds }}'
        and task_type = 'OVERALLS_CHECK'
        and substr(nvl(audit_time,complete_time),1,10) >= trunc('{{ execution_date | cst_ds }}', 'MM')
        AND substr(nvl(audit_time,complete_time),1,10) <= '{{ execution_date | cst_ds }}'
        and nvl(review_status,'*') in ('SUCCESS', 'FAIL', '*')
    group by
        substr(nvl(audit_time,complete_time),1,10),user_name,user_code

)
, telephone_summary as ( -- 打标电联率
    select
        dt as date_time
        ,delivery_staff_code
        ,delivery_staff_name
        ,sum(tel_cnt)      as tel_cnt
        ,sum(mark_cnt)     as mark_cnt
        ,round((sum(tel_cnt) / sum(mark_cnt)) * 100,2) as mark_tel_rate
    from
        jms_dm.dm_operate_area_mark_telephone_summary_day_dt
    where
        dt >= trunc('{{ execution_date | cst_ds }}', 'MM')
        AND dt <= '{{ execution_date | cst_ds }}'
    group by
        dt
        ,delivery_staff_code
        ,delivery_staff_name
)
, startup_question as ( -- 开机五道题答题计分
    select
        substr(create_time,1,10) as date_time
        ,staff_code
        ,staff_name
        ,1        as  question_pass_cnt    -- 只要这个报表内本日有该业务员数据,则该项则为答题正确,则计分,每天最多一分
    from
         jms_dwd.dwd_tab_exam_subject_record_list_dt
    where
         dt >= trunc('{{ execution_date | cst_ds }}', 'MM')
         AND dt <= '{{ execution_date | cst_ds }}'
    group by
        staff_code
        ,staff_name
        ,substr(create_time,1,10)
)
,   person_detail_info as (
         select
         delivery_code,
                NULL AS delivery_name ,
                date_time,
                sum(fwtd_num)as fwtd_num,
                sum(complaint_num)as complaint_num,
                sum(need_sign_count)as need_sign_count,
                sum(all_sign_22)as all_sign_22,
                sum(isSignRate)as isSignRate,
                sum(deliver_aging_sign_time)as deliver_aging_sign_time,
                sum(deliver_aging_sign_cnt)as deliver_aging_sign_cnt,
                sum(delver_sign_avg_hour)as delver_sign_avg_hour,
                sum(taking_success_sum)as taking_success_sum,
                sum(order_taking_fail_sum)as order_taking_fail_sum,
                sum(taking_success_rate)as taking_success_rate,
                sum(dispatch_loss_cnt) as dispatch_loss_cnt,
                sum(uniform_num) as uniform_num,
                sum(uniform_success_num) as uniform_success_num,
                sum(uniform_fail_num) as uniform_fail_num,
                sum(mark_cnt) as mark_cnt,
                sum(tel_cnt) as tel_cnt,
                sum(mark_tel_rate) as mark_tel_rate,
                sum(question_pass_cnt) as question_pass_cnt
         from (
        select
                delivery_code,  --派件员code
                delivery_name,  --派件员名字
                create_day as date_time ,     --业务日期
                fwtd_num,       -- 服务态度客诉
                complaint_num,  -- 投诉票数
                0 as  need_sign_count,  --应签收量
                0 as  all_sign_22,  --22点签收量
                0 as  isSignRate, --22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as  taking_success_sum,
                0 as  order_taking_fail_sum,
                0 as  taking_success_rate,
                0 as  dispatch_loss_cnt,
                0 as  uniform_num,
                0 as  uniform_success_num,
                0 as  uniform_fail_num,
                0 as  mark_cnt,  -- 打标量
                0 as  tel_cnt,   -- 电联量
                0 as mark_tel_rate, -- 打标电联率
                0 as  question_pass_cnt
         from staff_rank_detail
         union all
         select
                final_sign_user_code as delivery_code,
                final_sign_user_name as delivery_name,
                dt  as date_time,
                0 as   fwtd_num,       -- 服务态度客诉
                0 as   complaint_num,  -- 投诉票数
                need_sign_count,  --应签收量
                all_sign_22,  --22点签收量
                isSignRate, --22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as  taking_success_sum,
                0 as  order_taking_fail_sum,
                0 as  taking_success_rate,
                0 as  dispatch_loss_cnt,
                0 as  uniform_num,
                0 as  uniform_success_num,
                0 as  uniform_fail_num,
                0 as  mark_cnt,  -- 打标量
                0 as  tel_cnt,   -- 电联量
                0 as mark_tel_rate, -- 打标电联率
                0 as  question_pass_cnt
         from terminal_sign_summary_plan_sign

--          union all
--          select
--                 deliver_later_user_code  as delivery_code,
--                 deliver_later_user       as delivery_name ,
--                 sign_date  as date_time,
--                 0 as   fwtd_num,       -- 服务态度
--                 0 as   sfts_num,       -- 三方客诉
--                 0 as   signts_num,     -- 签收客诉
--                 0 as need_sign_count,  --应签收量
--                 0 as all_sign_22,  --22点签收量
--                 0 as isSignRate ,--22点签收率
--                 deliver_aging_sign_time ,--时效签收和末端网点派件时间差
--                 deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
--                 delver_sign_avg_hour,   --    派签平均时长（小时）
--                 0 as  taking_success_sum,
--                 0 as  order_taking_fail_sum,
--                 0 as  taking_success_rate
--          from duration_of_whole_process.

         union all
         select
                pick_staff_code  as delivery_code,
                pick_staff_name  as delivery_name,
                date_time as date_time,
                0 as   fwtd_num,       -- 服务态度
                0 as   complaint_num,  -- 投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                taking_success_sum,
                order_taking_fail_sum,
                taking_success_rate,
                0 as  dispatch_loss_cnt,
                0 as  uniform_num,
                0 as  uniform_success_num,
                0 as  uniform_fail_num,
                0 as  mark_cnt,  -- 打标量
                0 as  tel_cnt,   -- 电联量
                0 as mark_tel_rate, -- 打标电联率
                0 as  question_pass_cnt
         from outfield_person_order
         union all
         select
                dispatch_staff_code  as delivery_code,
                dispatch_staff_name  as delivery_name,
                date_time as date_time,
                0 as   fwtd_num,       -- 服务态度
                0 as   complaint_num,  -- 投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as taking_success_sum,
                0 as order_taking_fail_sum,
                0 as taking_success_rate,
                dispatch_loss_cnt as  dispatch_loss_cnt,
                0 as  uniform_num,
                0 as  uniform_success_num,
                0 as  uniform_fail_num,
                0 as  mark_cnt,  -- 打标量
                0 as  tel_cnt,   -- 电联量
                0 as mark_tel_rate, -- 打标电联率
                0 as  question_pass_cnt
         from
             dispatch_loss

         union all

         select
                user_code  as delivery_code,
                user_name  as delivery_name,
                date_time  as date_time,
                0 as   fwtd_num,       -- 服务态度
                0 as   complaint_num,  -- 投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as taking_success_sum,
                0 as order_taking_fail_sum,
                0 as taking_success_rate,
                0 as  dispatch_loss_cnt,
                uniform_num,
                uniform_success_num,
                uniform_fail_num,
                0 as  mark_cnt,  -- 打标量
                0 as  tel_cnt,   -- 电联量
                0 as  mark_tel_rate, -- 打标电联率
                0 as  question_pass_cnt
         from
             uniform_check

         union all

         select
                delivery_staff_code  as delivery_code,
                delivery_staff_name  as delivery_name,
                date_time  as date_time,
                0 as   fwtd_num,       -- 服务态度
                0 as   complaint_num,  -- 投诉票数
                0 as need_sign_count,  --应签收量
                0 as all_sign_22,  --22点签收量
                0 as isSignRate ,--22点签收率
                0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
                0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
                0 as delver_sign_avg_hour,   --    派签平均时长（小时）
                0 as taking_success_sum,
                0 as order_taking_fail_sum,
                0 as taking_success_rate,
                0 as dispatch_loss_cnt,
                0 as uniform_num,
                0 as uniform_success_num,
                0 as uniform_fail_num,
                mark_cnt,  -- 打标量
                tel_cnt,   -- 电联量
                mark_tel_rate, -- 打标电联率
                0 as question_pass_cnt
         from
            telephone_summary

         union all

         select
            staff_code  as delivery_code,
            staff_name  as delivery_name,
            date_time  as date_time,
            0 as   fwtd_num,       -- 服务态度
            0 as   complaint_num,  -- 投诉票数
            0 as need_sign_count,  --应签收量
            0 as all_sign_22,  --22点签收量
            0 as isSignRate ,--22点签收率
            0 as deliver_aging_sign_time ,--时效签收和末端网点派件时间差
            0 as deliver_aging_sign_cnt,    --时效签收和末端网点派件票数
            0 as delver_sign_avg_hour,   --    派签平均时长（小时）
            0 as taking_success_sum,
            0 as order_taking_fail_sum,
            0 as taking_success_rate,
            0 as dispatch_loss_cnt,
            0 as uniform_num,
            0 as uniform_success_num,
            0 as uniform_fail_num,
            0 as mark_cnt,  -- 打标量
            0 as tel_cnt,   -- 电联量
            0 as  mark_tel_rate, -- 打标电联率
            question_pass_cnt
         from
            startup_question
             ) tmp
         group by delivery_code,date_time
     ) ,
 network_whole_massage    as (
     select id,         --网点id
            code,       --网点code
            name,       --网点名字
            agent_code, --代理区code
            agent_name  --代理区名字
     from jms_dim.dim_network_whole_massage
     where  agent_name not like '%BEST%'
         )
insert overwrite table jms_dm.dm_star_staff_rnk_detail_dt partition (dt)
select
                 staff_code       --员工code
                ,staff_name       --员工名字
                ,network_code     --网点code
                ,network_name     --网点名称
                ,agent_code       --代理区code
                ,agent_name       --代理区
                ,0                   as date_type   -- 日期类型
                ,date_time                            -- 日期
                ,taking_success_num     --揽收成功量
                ,taking_fail_num        --揽收失败量
                ,taking_success_score   --散单揽收量计分
                ,taking_fail_score      --取消量计分
                ,need_sign_num          --时效签收应签收量
                ,sign_intime_num        --时效签收及时签收量
                ,round(sign_intime_rate,2)   as  sign_intime_rate   --时效签收准点率
                ,round(sign_intime_score,2)  as  sign_intime_score  --时效签收准点率得分
                ,complaint_num          --客诉数量
                ,fwtd_num               --服务态度投诉
                ,complaint_score        --客诉计分
                ,uniform_num            --工服抽查次数
                ,uniform_score          --工服抽查次数计分
                ,round((taking_success_score + taking_fail_score + sign_intime_score + complaint_score + uniform_score + dispatch_loss_score + mark_tel_score + question_pass_score),2 ) as total_score -- 总分
                ,null as star_level -- 星级, 日维度不展示
                ,row_number() over (partition by network_code, date_time order by
                        (
                        taking_success_score + taking_fail_score + sign_intime_score + complaint_score + uniform_score + dispatch_loss_score + mark_tel_score + question_pass_score
                        ) desc,sign_intime_rate desc,taking_success_num desc ) as network_rank  --网点排名
                ,row_number() over (partition by agent_code, date_time order by
                        (
                        taking_success_score + taking_fail_score + sign_intime_score + complaint_score + uniform_score + dispatch_loss_score + mark_tel_score + question_pass_score
                        ) desc,sign_intime_rate desc,taking_success_num desc ) as agent_rank    --代理区排名
                ,row_number() over (partition by date_time order by
                        (
                        taking_success_score + taking_fail_score + sign_intime_score + complaint_score + uniform_score + dispatch_loss_score + mark_tel_score + question_pass_score
                        ) desc,sign_intime_rate desc,taking_success_num desc ) as country_rank  --全国排名
                ,dispatch_loss_cnt       --派送途中遗失数
                ,dispatch_loss_score     --派送途中遗失扣分
                ,hire_time               --入职时间
                ,is_new                  --是否新员工
                ,uniform_success_num     --工服抽查通过数
                ,uniform_success_score   --工服抽查通过计分
                ,uniform_fail_num        --工服抽查不通过数
                ,uniform_fail_score      --工服抽查不通过计分
                ,mark_cnt                --打标数
                ,tel_cnt                 --电联数
                ,mark_tel_rate           --打标电联率
                ,mark_tel_score          --打标电联分,天维度不计分
                ,question_pass_cnt       --开机五道题通过数
                ,question_pass_score     --开机五道题得分
                ,count(1) over (partition by agent_code, date_time) as agent_staff_cnt
                ,count(1) over (partition by date_time)             as country_staff_cnt
                ,null                                               as next_star_need_score
                ,round( -- 总加分
                      taking_success_score
                    + sign_intime_score
                    + uniform_success_score
                    + mark_tel_score
                    + question_pass_score
                 ,2)               as total_add_score
                ,round( -- 总扣分
                      taking_fail_score
                    + complaint_score
                    + uniform_fail_score
                    + dispatch_loss_score
                 ,2)               as total_deduct_score
                ,dt
from (
            SELECT  a.staff_code --员工code
                   ,a.staff_name --员工名字
                   ,network.code                    AS network_code --网点code
                   ,network.name                    AS network_name --网点名称
                   ,network.agent_code     --代理区code
                   ,network.agent_name     --代理区名字
                   ,b.date_time            --业务日期
                   ,nvl(b.taking_success_sum,0)     AS taking_success_num    --揽收成功量
                   ,nvl(b.order_taking_fail_sum,0)  AS taking_fail_num       --揽收失败量
                   ,case
                        when config.throwaway_collect_is_enable = 0 then 0
                        --  when nvl(b.order_taking_fail_sum * config.cancel_score,0) < config.cancel_score_limit then 0
                        else least(nvl(b.taking_success_sum * config.throwaway_collect_score,0), config.throwaway_collect_score_limit )
                    end                                                 AS taking_success_score  --散单揽收量计分
                   ,greatest(nvl(b.order_taking_fail_sum * config.cancel_score,0), config.cancel_score_limit) * config.cancel_is_enable AS taking_fail_score     --揽收失败量计分
                   ,nvl(b.need_sign_count,0)    AS need_sign_num         --时效签收应签收量
                   ,nvl(b.all_sign_22,0)        AS sign_intime_num       --时效签收及时签收量
                   ,nvl(b.isSignRate,0)         AS sign_intime_rate      --时效签收准点率
                   -- 时效签收准点率评分 保底值96.5%, 目标值97.5%
                   ,nvl(round(case when config.timely_sign_is_enable = 0 then 0
                         when nvl(b.all_sign_22,0) <  config.timely_sign_one_votes_min then 0
                         when b.all_sign_22 >= config.timely_sign_one_votes_min and b.all_sign_22 < config.timely_sign_one_votes_max then
                                 case
                                     when nvl(b.isSignRate, 0) >= config.timely_sign_one_target then config.timely_sign_one_score
                                     when nvl(b.isSignRate, 0) <  config.timely_sign_one_floors then 0
                                     else (b.isSignRate - config.timely_sign_one_floors) / (config.timely_sign_one_target - config.timely_sign_one_floors) * config.timely_sign_one_score
                                 end
                         when b.all_sign_22 >= config.timely_sign_two_votes_min and b.all_sign_22 < config.timely_sign_two_votes_max then
                                 case
                                     when nvl(b.isSignRate, 0) >=config.timely_sign_two_target then config.timely_sign_two_score
                                     when nvl(b.isSignRate, 0) < config.timely_sign_two_floors then 0
                                     else (b.isSignRate - config.timely_sign_two_floors) / (config.timely_sign_two_target - config.timely_sign_two_floors) * config.timely_sign_two_score
                                 end
                        when b.all_sign_22 >= config.timely_sign_three_votes_min and b.all_sign_22 < config.timely_sign_three_votes_max then
                                 case
                                     when nvl(b.isSignRate, 0) >= config.timely_sign_three_target then config.timely_sign_three_score
                                     when nvl(b.isSignRate, 0) <  config.timely_sign_three_floors then 0
                                     else (b.isSignRate - config.timely_sign_three_floors ) / (config.timely_sign_three_target - config.timely_sign_three_floors) * config.timely_sign_three_score
                                 end
                    end,2)
                    ,0)                                              AS sign_intime_score       -- 时效签收准点率计分
                   ,nvl(b.complaint_num, 0)                          AS complaint_num           -- 客诉次数
                   ,nvl(b.fwtd_num, 0)                               AS fwtd_num                -- 服务态度投诉次数
                    -- 一级问题类型：投诉：月累计投诉次数<=3，-20分/单；月累计投诉次数>3，超过部分-30分/单
                    -- 二级问题类型：服务态度：在原有单号扣分基础上累加-10/单
                   ,(nvl(b.complaint_num, 0) * config.customer_complaint_score + (nvl(b.fwtd_num, 0) * -10))
                        * config.customer_complaint_is_enable                                AS complaint_score         --客诉次数计分
                   ,dispatch_loss_cnt                                                        as dispatch_loss_cnt       --派送途中遗失数
                   ,dispatch_loss_cnt * config.lose_score * config.lose_is_enable            as dispatch_loss_score     --派送途中遗失扣分
                   ,a.hire_time
                   ,a.is_new
                   -- 工服抽查计分: 通过分和不通过分先封顶再相加
                   ,uniform_num                                                              AS uniform_num             --工服抽查次数
                   -- ,(uniform_success_num * config.work_clothes_check_add_score + uniform_fail_num * config.work_clothes_check_sub_score)
                   --    * config.work_clothes_check_is_enable                                  AS uniform_score           --工服抽查次数计分
                   ,least(b.uniform_success_num * config.work_clothes_check_add_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable
                    + greatest(b.uniform_fail_num * config.work_clothes_check_sub_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable as uniform_score
                   ,b.uniform_success_num
                   ,least(b.uniform_success_num * config.work_clothes_check_add_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable   as uniform_success_score  -- 工服抽查通过计分
                   ,b.uniform_fail_num
                   ,greatest(b.uniform_fail_num * config.work_clothes_check_sub_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable   as uniform_fail_score     -- 工服抽查不通过扣分
                   ,b.mark_cnt
                   ,b.tel_cnt
                   ,b.mark_tel_rate
                   ,0  as mark_tel_score -- 天维度不计分
                   ,b.question_pass_cnt
                   ,b.question_pass_cnt * config.five_question_score * config.five_question_enable as question_pass_score
                   ,'{{ execution_date | cst_ds }}' AS dt
            FROM lmdm_sys_staff a
                  join person_detail_info b
                            on a.staff_code = b.delivery_code
                  join network_whole_massage network
                            on a.network_id = network.id
                  left join jms_dim.dim_tab_ops_star_rank_config_dt config
                            on date_time >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                           and date_time <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                           and config.dt = '{{ execution_date | cst_ds }}'
     )tmp
distribute by dt , pmod(hash(rand()),10);




-- 月汇总明细：上月汇总
insert into table jms_dm.dm_star_staff_rnk_detail_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    a.staff_code
    , a.staff_name
    , a.network_code
    , a.network_name
    , a.agent_code
    , a.agent_name
    , a.date_type
    , a.date_time
    , a.taking_success_num
    , a.taking_fail_num
    , a.taking_success_score
    , a.taking_fail_score
    , a.need_sign_num
    , a.sign_intime_num
    , null as sign_intime_rate -- 月维度时效签收准点率达成率不展示
    , a.sign_intime_score
    , a.complaint_num
    , a.fwtd_num
    , a.complaint_score
    , a.uniform_num
    , (a.uniform_success_score + uniform_fail_score) as uniform_score
    , a.total_score
    , case
           -- when black_list.staff_code is not null then 1 -- 在黑名单默认为星级1
           when total_score  < config.one_star_score_max    then 1
           when total_score  >= config.two_star_score_min   and total_score < config.two_star_score_max   then 2
           when total_score  >= config.three_star_score_min and total_score < config.three_star_score_max then 3
           when total_score  >= config.four_star_score_min  and total_score < config.four_star_score_max  then 4
           when total_score  >= config.five_star_score_min  and total_score < config.five_star_score_max  then 5
     end                           as star_level              -- 星级
     ,row_number() over (partition by a.network_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as network_rank  --网点排名
     ,row_number() over (partition by a.agent_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as agent_rank    --代理区排名
     ,row_number() over (partition by date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as country_rank  --全国排名
    ,dispatch_loss_cnt
    ,dispatch_loss_score
    ,hire_time
    ,is_new
    ,uniform_success_num     --工服抽查通过数
    ,uniform_success_score   --工服抽查通过计分
    ,uniform_fail_num        --工服抽查不通过数
    ,uniform_fail_score      --工服抽查不通过计分
    ,mark_cnt                --打标数
    ,tel_cnt                 --电联数
    ,mark_tel_rate           --打标电联率
    ,mark_tel_score          --打标电联分
    ,question_pass_cnt       --开机五道题通过数
    ,question_pass_score     --开机五道题得分
    ,count(1) over (partition by agent_code, date_time) as agent_staff_cnt
    ,count(1) over (partition by date_time)             as country_staff_cnt
    ,round(case
           -- when black_list.staff_code is not null then 1 -- 在黑名单默认为星级1
           when total_score  <  config.one_star_score_min   then config.two_star_score_min - total_score
           when total_score  >= config.one_star_score_min   and total_score < config.one_star_score_max   then config.two_star_score_min   - total_score
           when total_score  >= config.two_star_score_min   and total_score < config.two_star_score_max   then config.three_star_score_min - total_score
           when total_score  >= config.three_star_score_min and total_score < config.three_star_score_max then config.four_star_score_min  - total_score
           when total_score  >= config.four_star_score_min  and total_score < config.four_star_score_max  then config.five_star_score_min  - total_score
           when total_score  >= config.five_star_score_min  and total_score < config.five_star_score_max  then 0
     end,2)                           as next_star_need_score -- 下一星级需要分数
    ,round( -- 总加分
          taking_success_score
        + sign_intime_score
        + uniform_success_score
        + mark_tel_score
        + question_pass_score
     ,2)               as total_add_score
    ,round( -- 总扣分
          taking_fail_score
        + complaint_score
        + uniform_fail_score
        + dispatch_loss_score
     ,2)               as total_deduct_score
from (
    select
          staff_code
        , staff_name
        , network_code
        , network_name
        , agent_code
        , agent_name
        , date_type
        , date_time
        , taking_success_num
        , taking_fail_num
        , taking_success_score
        , taking_fail_score
        , need_sign_num
        , sign_intime_num
        , sign_intime_rate
        , sign_intime_score
        , complaint_num
        , fwtd_num
        , complaint_score
        , uniform_num
        , uniform_success_num
        , least(uniform_success_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable  as uniform_success_score
        , uniform_fail_num
        , greatest(uniform_fail_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable  as uniform_fail_score
        ,dispatch_loss_cnt
        ,dispatch_loss_score
        ,round(
            total_score
          + least(uniform_success_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable
          + greatest(uniform_fail_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable
          + (
                case
                   when config.label_must_call_rate_enable = 0 then 0
                   when  nvl(mark_tel_rate, 0) < config.label_must_call_rate_floors  then 0
                   when  nvl(mark_tel_rate, 0) >= config.label_must_call_rate_target then config.label_must_call_rate_score
                   else (nvl(mark_tel_rate, 0) - config.label_must_call_rate_floors) / (config.label_must_call_rate_target - config.label_must_call_rate_floors) * config.label_must_call_rate_score
                 end
          ),2)    as total_score
        ,hire_time
        ,is_new
        ,mark_cnt
        ,tel_cnt
        ,mark_tel_rate
        ,case
             when config.label_must_call_rate_enable = 0 then 0
             when  nvl(mark_tel_rate, 0) < config.label_must_call_rate_floors  then 0
             when  nvl(mark_tel_rate, 0) >= config.label_must_call_rate_target then config.label_must_call_rate_score
             else (nvl(mark_tel_rate, 0) - config.label_must_call_rate_floors) / (config.label_must_call_rate_target - config.label_must_call_rate_floors) * config.label_must_call_rate_score
        end          as mark_tel_score
        ,question_pass_cnt
        ,question_pass_score
      from(
          select staff_code                                                                                          -- 员工code
               , staff_name                                                                                          -- 员工name
               , network_code
               , network_name
               , agent_code
               , agent_name
               , 1                                                                           as date_type
               , '{{ execution_date | cst_ds }}'                                             as date_time
               , sum(taking_success_num)                                                     as taking_success_num   -- 揽收成功量
               , sum(taking_fail_num)                                                        as taking_fail_num      -- 揽收失败量
               , sum(taking_success_score)                                                   as taking_success_score -- 散单揽收量计分
               , sum(taking_fail_score)                                                      as taking_fail_score    -- 取消量计分
               , sum(need_sign_num)                                                          as need_sign_num        -- 时效签收应签收量
               , sum(sign_intime_num)                                                        as sign_intime_num      -- 时效签收及时签收量
               , coalesce(round((sum(sign_intime_num) / sum(need_sign_num)) * 100, 2), 0.00) as sign_intime_rate     -- 时效签收准点率
               , sum(sign_intime_score)                                                      as sign_intime_score    -- 时效签收准点率得分
               , sum(complaint_num)                                                          as complaint_num        -- 客诉数量
               , sum(fwtd_num)                                                               as fwtd_num             -- 服务态度投诉次数
               , sum(complaint_score)                                                        AS complaint_score      -- 客诉次数计分
               , sum(uniform_num)                                                            as uniform_num          -- 工服抽查次数
               , sum(uniform_score)                                                          as uniform_score        -- 工服抽查次数计分
               , sum(uniform_success_num)                                                    as uniform_success_num  -- 工服抽查通过数
               , sum(uniform_success_score)                                                  as uniform_success_score-- 工服抽查通过计分
               , sum(uniform_fail_num)                                                       as uniform_fail_num     -- 工服抽查不通过数
               , sum(uniform_fail_score)                                                     as uniform_fail_score   -- 工服抽查不通过计分
               , sum(dispatch_loss_cnt)                                                      as dispatch_loss_cnt    -- 派送途中遗失数
               , sum(dispatch_loss_score)                                                    as dispatch_loss_score  -- 派送途中遗失扣分
               , sum(taking_success_score) + sum(taking_fail_score) + sum(sign_intime_score) + sum(complaint_score)
                 + sum(dispatch_loss_score) + sum(question_pass_score)                       as total_score          -- 总分
               ,hire_time
               ,is_new
               ,sum(mark_cnt)                                                                as mark_cnt             --打标数
               ,sum(tel_cnt)                                                                 as tel_cnt              --电联数
               ,round((sum(tel_cnt) / sum(mark_cnt)) * 100,2)                                as mark_tel_rate        --电联率
               ,sum(question_pass_cnt)                                                       as question_pass_cnt    --开机五道题通过数
               ,sum(question_pass_score)                                                     as question_pass_score  --开机五道题得分
          from jms_dm.dm_star_staff_rnk_detail_dt
          where dt = '{{ execution_date | cst_ds }}'
            and substr(date_add('{{ execution_date | cst_ds }}', 1), 9, 2) = '01'
            and date_type = '0' -- 0: 日维度
            and substr(date_time, 1, 7) = substr('{{ execution_date | cst_ds }}', 1, 7)
          group by staff_code
                 , staff_name
                 , network_code
                 , network_name
                 , agent_code
                 , agent_name
                 , substr(date_time, 1, 7)
                 ,hire_time
                 ,is_new
    ) a  left join jms_dim.dim_tab_ops_star_rank_config_dt config -- 月汇总用配置表最新数据
                                on '{{ execution_date | cst_ds }}' >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                               and '{{ execution_date | cst_ds }}' <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                               and config.dt = '{{ execution_date | cst_ds }}'
) a  left join jms_dim.dim_tab_ops_star_rank_config_dt config -- 月汇总用配置表最新数据
                            on '{{ execution_date | cst_ds }}' >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                           and '{{ execution_date | cst_ds }}' <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                           and config.dt = '{{ execution_date | cst_ds }}'
     -- left join  jms_dim.star_rank_black_white_list black_list
     --                        on  a.staff_code = black_list.staff_code
     --                       and  substr('{{ execution_date | cst_ds }}', 1, 7) <= nvl(substr(black_list.delete_time,1,7), '9999-99')
     --                       and  substr('{{ execution_date | cst_ds }}', 1, 7) >= substr(black_list.create_time,1,7)
     --                       and  black_list.type = 1
distribute by 1
;




-- 月汇总明细：本月汇总
insert into table jms_dm.dm_star_staff_rnk_detail_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    a.staff_code
    , a.staff_name
    , a.network_code
    , a.network_name
    , a.agent_code
    , a.agent_name
    , a.date_type
    , a.date_time
    , a.taking_success_num
    , a.taking_fail_num
    , a.taking_success_score
    , a.taking_fail_score
    , a.need_sign_num
    , a.sign_intime_num
    , null as sign_intime_rate -- 月维度时效签收准点率达成率不展示
    , a.sign_intime_score
    , a.complaint_num
    , a.fwtd_num
    , a.complaint_score
    , a.uniform_num
    , (a.uniform_success_score + uniform_fail_score) as uniform_score
    , a.total_score
    , case
           -- when black_list.staff_code is not null then 1 -- 在黑名单默认为星级1
           when total_score < config.one_star_score_max   then 1
           when total_score  >= config.two_star_score_min   and total_score < config.two_star_score_max   then 2
           when total_score  >= config.three_star_score_min and total_score < config.three_star_score_max then 3
           when total_score  >= config.four_star_score_min  and total_score < config.four_star_score_max  then 4
           when total_score  >= config.five_star_score_min  and total_score < config.five_star_score_max  then 5
     end                           as star_level              -- 星级
     ,row_number() over (partition by a.network_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as network_rank  --网点排名
     ,row_number() over (partition by a.agent_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as agent_rank    --代理区排名
     ,row_number() over (partition by date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as country_rank  --全国排名
    ,dispatch_loss_cnt
    ,dispatch_loss_score
    ,hire_time
    ,is_new
    ,uniform_success_num     --工服抽查通过数
    ,uniform_success_score   --工服抽查通过计分
    ,uniform_fail_num        --工服抽查不通过数
    ,uniform_fail_score      --工服抽查不通过计分
    ,mark_cnt                --打标数
    ,tel_cnt                 --电联数
    ,mark_tel_rate           --打标电联率
    ,mark_tel_score          --打标电联分
    ,question_pass_cnt       --开机五道题通过数
    ,question_pass_score     --开机五道题得分
    ,count(1) over (partition by agent_code, date_time) as agent_staff_cnt
    ,count(1) over (partition by date_time)             as country_staff_cnt
    ,round(case
           -- when black_list.staff_code is not null then 1 -- 在黑名单默认为星级1
           when total_score  <  config.one_star_score_min   then config.two_star_score_min - total_score
           when total_score  >= config.one_star_score_min   and total_score < config.one_star_score_max   then config.two_star_score_min   - total_score
           when total_score  >= config.two_star_score_min   and total_score < config.two_star_score_max   then config.three_star_score_min - total_score
           when total_score  >= config.three_star_score_min and total_score < config.three_star_score_max then config.four_star_score_min  - total_score
           when total_score  >= config.four_star_score_min  and total_score < config.four_star_score_max  then config.five_star_score_min  - total_score
           when total_score  >= config.five_star_score_min  and total_score < config.five_star_score_max  then 0
     end,2)                           as next_star_need_score -- 下一星级需要分数
    ,round( -- 总加分
          taking_success_score
        + sign_intime_score
        + uniform_success_score
        + mark_tel_score
        + question_pass_score
     ,2)               as total_add_score
    ,round( -- 总扣分
          taking_fail_score
        + complaint_score
        + uniform_fail_score
        + dispatch_loss_score
     ,2)               as total_deduct_score
from (
    select
          staff_code
        , staff_name
        , network_code
        , network_name
        , agent_code
        , agent_name
        , date_type
        , date_time
        , taking_success_num
        , taking_fail_num
        , taking_success_score
        , taking_fail_score
        , need_sign_num
        , sign_intime_num
        , sign_intime_rate
        , sign_intime_score
        , complaint_num
        , fwtd_num
        , complaint_score
        , uniform_num
        , uniform_success_num
        , least(uniform_success_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable  as uniform_success_score
        , uniform_fail_num
        , greatest(uniform_fail_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable  as uniform_fail_score
        ,dispatch_loss_cnt
        ,dispatch_loss_score
        ,round(
            total_score
          + least(uniform_success_score, config.work_clothes_check_add_limit) * config.work_clothes_check_is_enable
          + greatest(uniform_fail_score, config.work_clothes_check_sub_limit) * config.work_clothes_check_is_enable
          + (
                case
                   when config.label_must_call_rate_enable = 0 then 0
                   when  nvl(mark_tel_rate, 0) < config.label_must_call_rate_floors  then 0
                   when  nvl(mark_tel_rate, 0) >= config.label_must_call_rate_target then config.label_must_call_rate_score
                   else (nvl(mark_tel_rate, 0) - config.label_must_call_rate_floors) / (config.label_must_call_rate_target - config.label_must_call_rate_floors) * config.label_must_call_rate_score
                 end
          ),2)    as total_score
        ,hire_time
        ,is_new
        ,mark_cnt
        ,tel_cnt
        ,mark_tel_rate
        ,case
             when config.label_must_call_rate_enable = 0 then 0
             when  nvl(mark_tel_rate, 0) < config.label_must_call_rate_floors  then 0
             when  nvl(mark_tel_rate, 0) >= config.label_must_call_rate_target then config.label_must_call_rate_score
             else (nvl(mark_tel_rate, 0) - config.label_must_call_rate_floors) / (config.label_must_call_rate_target - config.label_must_call_rate_floors) * config.label_must_call_rate_score
        end          as mark_tel_score
        ,question_pass_cnt
        ,question_pass_score
      from(
          select staff_code                                                                                          -- 员工code
               , staff_name                                                                                          -- 员工name
               , network_code
               , network_name
               , agent_code
               , agent_name
               , 3                                                                           as date_type
               , last_day('{{ execution_date | cst_ds }}')                                   as date_time
               , sum(taking_success_num)                                                     as taking_success_num   -- 揽收成功量
               , sum(taking_fail_num)                                                        as taking_fail_num      -- 揽收失败量
               , sum(taking_success_score)                                                   as taking_success_score -- 散单揽收量计分
               , sum(taking_fail_score)                                                      as taking_fail_score    -- 取消量计分
               , sum(need_sign_num)                                                          as need_sign_num        -- 时效签收应签收量
               , sum(sign_intime_num)                                                        as sign_intime_num      -- 时效签收及时签收量
               , coalesce(round((sum(sign_intime_num) / sum(need_sign_num)) * 100, 2), 0.00) as sign_intime_rate     -- 时效签收准点率
               , sum(sign_intime_score)                                                      as sign_intime_score    -- 时效签收准点率得分
               , sum(complaint_num)                                                          as complaint_num        -- 客诉数量
               , sum(fwtd_num)                                                               as fwtd_num             -- 服务态度投诉次数
               , sum(complaint_score)                                                        AS complaint_score      -- 客诉次数计分
               , sum(uniform_num)                                                            as uniform_num          -- 工服抽查次数
               , sum(uniform_score)                                                          as uniform_score        -- 工服抽查次数计分
               , sum(uniform_success_num)                                                    as uniform_success_num  -- 工服抽查通过数
               , sum(uniform_success_score)                                                  as uniform_success_score-- 工服抽查通过计分
               , sum(uniform_fail_num)                                                       as uniform_fail_num     -- 工服抽查不通过数
               , sum(uniform_fail_score)                                                     as uniform_fail_score   -- 工服抽查不通过计分
               , sum(dispatch_loss_cnt)                                                      as dispatch_loss_cnt    -- 派送途中遗失数
               , sum(dispatch_loss_score)                                                    as dispatch_loss_score  -- 派送途中遗失扣分
               , sum(taking_success_score) + sum(taking_fail_score) + sum(sign_intime_score) + sum(complaint_score)
                 + sum(dispatch_loss_score) + sum(question_pass_score)                       as total_score          -- 总分
               ,hire_time
               ,is_new
               ,sum(mark_cnt)                                                                as mark_cnt             --打标数
               ,sum(tel_cnt)                                                                 as tel_cnt              --电联数
               ,round((sum(tel_cnt) / sum(mark_cnt)) * 100,2)                                as mark_tel_rate        --电联率
               ,sum(question_pass_cnt)                                                       as question_pass_cnt    --开机五道题通过数
               ,sum(question_pass_score)                                                     as question_pass_score  --开机五道题得分
          from jms_dm.dm_star_staff_rnk_detail_dt
          where
                dt = '{{ execution_date | cst_ds }}'
            and date_time >= trunc('{{ execution_date | cst_ds }}', 'MM')
            and date_time <= '{{ execution_date | cst_ds }}'
            and date_type = '0' -- 0: 日维度
            and substr(date_time, 1, 7) = substr('{{ execution_date | cst_ds }}', 1, 7)
          group by staff_code
                 , staff_name
                 , network_code
                 , network_name
                 , agent_code
                 , agent_name
                 , substr(date_time, 1, 7)
                 , hire_time
                 , is_new
    ) a  left join jms_dim.dim_tab_ops_star_rank_config_dt config -- 月汇总用配置表最新数据
                                on '{{ execution_date | cst_ds }}' >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                               and '{{ execution_date | cst_ds }}' <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                               and config.dt = '{{ execution_date | cst_ds }}'
) a  left join jms_dim.dim_tab_ops_star_rank_config_dt config -- 月汇总用配置表最新数据
                            on '{{ execution_date | cst_ds }}' >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                           and '{{ execution_date | cst_ds }}' <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                           and config.dt = '{{ execution_date | cst_ds }}'
     -- left join  jms_dim.star_rank_black_white_list black_list
     --                        on  a.staff_code = black_list.staff_code
     --                       and  substr('{{ execution_date | cst_ds }}', 1, 7) <= nvl(substr(black_list.delete_time,1,7), '9999-99')
     --                       and  substr('{{ execution_date | cst_ds }}', 1, 7) >= substr(black_list.create_time,1,7)
     --                       and  black_list.type = 1
distribute by 1
;

-- 季度汇总明细
insert into table jms_dm.dm_star_staff_rnk_detail_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    staff_code
    , staff_name
    , network_code
    , network_name
    , agent_code
    , agent_name
    , date_type
    , date_time
    , taking_success_num
    , taking_fail_num
    , taking_success_score
    , taking_fail_score
    , need_sign_num
    , sign_intime_num
    , null as sign_intime_rate -- 季维度时效签收准点率达成率不展示
    , sign_intime_score
    , complaint_num
    , fwtd_num
    , complaint_score
    , uniform_num
    , uniform_score
    , total_score
    , case when total_score < config.one_star_score_max   then 1
           when total_score >= config.two_star_score_min   and total_score < config.two_star_score_max   then 2
           when total_score >= config.three_star_score_min and total_score < config.three_star_score_max then 3
           when total_score >= config.four_star_score_min  and total_score < config.four_star_score_max  then 4
           when total_score >= config.five_star_score_min  and total_score < config.five_star_score_max  then 5
      end                           as star_level              -- 星级
--      , case
--            when total_score  < 500   then 1
--            when total_score  >= 500  and total_score < 1000 then 2
--            when total_score  >= 1000 and total_score < 2500 then 3
--            when total_score  >= 2500 and total_score < 2800 then 4
--            when total_score  >= 2800 then 5
--      end                           as star_level              -- 星级
     ,row_number() over (partition by network_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as network_rank  --网点排名
     ,row_number() over (partition by agent_code, date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as agent_rank    --代理区排名
     ,row_number() over (partition by date_time order by
                                   total_score desc
                                  ,sign_intime_rate desc
                                  ,taking_success_num desc
                        ) as country_rank  --全国排名
    , dispatch_loss_cnt     -- 派送途中遗失数
    , dispatch_loss_score   -- 派送途中遗失扣分
    , null as hire_time     -- 季度维度置空
    , null as is_new        -- 季度维度置空
    , uniform_success_num   --工服抽查通过数
    , uniform_success_score --工服抽查通过计分
    , uniform_fail_num      --工服抽查不通过数
    , uniform_fail_score    --工服抽查不通过计分
    , mark_cnt
    , tel_cnt
    , mark_tel_rate        -- 打标电联率
    , mark_tel_score
    , question_pass_cnt
    , question_pass_score
    , count(1) over (partition by agent_code, date_time) as agent_staff_cnt
    , count(1) over (partition by date_time)             as country_staff_cnt
    , round(case
           -- when black_list.staff_code is not null then 1 -- 在黑名单默认为星级1
           when total_score  <  config.one_star_score_min   then config.two_star_score_min - total_score
           when total_score  >= config.one_star_score_min   and total_score < config.one_star_score_max   then config.two_star_score_min   - total_score
           when total_score  >= config.two_star_score_min   and total_score < config.two_star_score_max   then config.three_star_score_min - total_score
           when total_score  >= config.three_star_score_min and total_score < config.three_star_score_max then config.four_star_score_min  - total_score
           when total_score  >= config.four_star_score_min  and total_score < config.four_star_score_max  then config.five_star_score_min  - total_score
           when total_score  >= config.five_star_score_min  and total_score < config.five_star_score_max  then 0
     end,2)                           as next_star_need_score -- 下一星级需要分数
    ,total_add_score
    ,total_deduct_score
from (
      select staff_code                                                                                          -- 员工code
           , staff_name                                                                                          -- 员工name
           , network_code
           , network_name
           , agent_code
           , agent_name
           , 2                                                                           as date_type
           , '{{ execution_date | cst_ds }}'                                             as date_time
           , sum(taking_success_num)                                                     as taking_success_num   -- 揽收成功量
           , sum(taking_fail_num)                                                        as taking_fail_num      -- 揽收失败量
           , sum(taking_success_score)                                                   as taking_success_score -- 散单揽收量计分
           , sum(taking_fail_score)                                                      as taking_fail_score    -- 取消量计分
           , sum(need_sign_num)                                                          as need_sign_num        -- 时效签收应签收量
           , sum(sign_intime_num)                                                        as sign_intime_num      -- 时效签收及时签收量
           , coalesce(round((sum(sign_intime_num) / sum(need_sign_num)) * 100, 2), 0.00) as sign_intime_rate     -- 时效签收准点率
           , sum(sign_intime_score)                                                      as sign_intime_score    -- 时效签收准点率得分
           , sum(complaint_num)                                                          as complaint_num        -- 客诉数量
           , sum(fwtd_num)                                                               as fwtd_num             -- 服务态度投诉次数
           , sum(complaint_score)                                                        as complaint_score      -- 客诉次数计分
           , sum(uniform_num)                                                            as uniform_num          -- 工服抽查次数
           , sum(uniform_score)                                                          as uniform_score        -- 工服抽查次数计分
           , sum(uniform_success_num)                                                    as uniform_success_num  -- 工服抽查通过数
           , sum(uniform_success_score)                                                  as uniform_success_score-- 工服抽查通过计分
           , sum(uniform_fail_num)                                                       as uniform_fail_num     -- 工服抽查不通过数
           , sum(uniform_fail_score)                                                     as uniform_fail_score   -- 工服抽查不通过计分
           , sum(dispatch_loss_cnt)                                                      as dispatch_loss_cnt    -- 派送途中遗失数
           , sum(dispatch_loss_score)                                                    as dispatch_loss_score  -- 派送途中遗失扣分
           , sum(total_score)                                                            as total_score          -- 总分
           , sum(mark_cnt)                                                               as mark_cnt             -- 打标数
           , sum(tel_cnt)                                                                as tel_cnt              -- 电联数
           , sum(mark_tel_score)                                                         as mark_tel_score       -- 打标电联分
           , round((sum(tel_cnt) / sum(mark_cnt)) * 100,2)                               as mark_tel_rate        -- 电联率
           , sum(question_pass_cnt)                                                      as question_pass_cnt    -- 开机五道题通过数
           , sum(question_pass_score)                                                    as question_pass_score  -- 开机五道题得分
           , sum(total_add_score)                                                        as total_add_score      -- 总加分
           , sum(total_deduct_score)                                                     as total_deduct_score   -- 总扣分
      from jms_dm.dm_star_staff_rnk_detail_dt
      where
            substr('{{ execution_date | cst_ds }}', 6, 5) in ('03-31', '06-30', '09-30', '12-31')
            and dt in (
                least('{{ execution_date | cst_ds }}',string(last_day(
                                                                CONCAT(
                                                                        YEAR('{{ execution_date | cst_ds }}')
                                                                        ,'-'
                                                                        ,LPAD(FLOOR((MONTH('{{ execution_date | cst_ds }}') - 1) / 3) * 3 + 1, 2, '0')
                                                                        ,'-01'
                                                                    )
                                                                ))
                )  -- 季度第一月最后一天或调度日期
                ,least('{{ execution_date | cst_ds }}',string(last_day(
                                                                add_months(
                                                                    CONCAT(
                                                                            YEAR('{{ execution_date | cst_ds }}')
                                                                            ,'-'
                                                                            ,LPAD(FLOOR((MONTH('{{ execution_date | cst_ds }}') - 1) / 3) * 3 + 1, 2, '0')
                                                                            ,'-01'
                                                                        )
                                                                    ,1)
                                                        ))
                )   -- 季度第二月最后一天或调度日期
                ,least('{{ execution_date | cst_ds }}',string(last_day(
                                                                add_months(
                                                                    CONCAT(
                                                                            YEAR('{{ execution_date | cst_ds }}')
                                                                            ,'-'
                                                                            ,LPAD(FLOOR((MONTH('{{ execution_date | cst_ds }}') - 1) / 3) * 3 + 1, 2, '0')
                                                                            ,'-01'
                                                                        )
                                                                    ,2)
                                                                ))
                )   -- 季度第三月最后一天或调度日期
        ) 
        and date_type = '1' -- 1: 月维度
      group by staff_code
             , staff_name
             , network_code
             , network_name
             , agent_code
             , agent_name
) a left join jms_dim.dim_tab_ops_star_rank_config_dt config
                            on '{{ execution_date | cst_ds }}' >= date_format(config.effect_start_time, 'yyyy-MM-dd')
                           and '{{ execution_date | cst_ds }}' <= date_format(config.effect_end_time, 'yyyy-MM-dd')
                           and config.dt = '{{ execution_date | cst_ds }}'
distribute by 1
;